﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using DapperExtensions;
using BLevy.Entidad;
using BLevy.Datos.Interfases;
using System.Data;
using MySql.Data.MySqlClient;
using System.Configuration;

namespace BLevy.Datos.Repositories
{
    public interface IDeudaRepository : IRepository<Deuda>
    {
        int RegistrarDeuda(Deuda _deuda);
        double VerificarDeuda(int idSocio);
        Deuda ConsultarDeudaPorSocio(Socio socio);
        int ModificarDeuda(Deuda _deuda);
    }

    public class DeudaRepository : IDeudaRepository
    {
        protected readonly IDbConnection _db;

        public DeudaRepository()
        {
            try
            {
                _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString());
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int RegistrarDeuda(Deuda _deuda)
        {
            string query = "INSERT INTO COBROSDEUDAS (COBRO_ID, SALDO, FECHA, ESTADO) " +
                        "VALUES (@cobro_id, @saldo, @fecha, @estado);" +
                        "SELECT LAST_INSERT_ID();";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<int>(query, new
                    {
                        cobro_id = _deuda.cobro_id,
                        saldo = _deuda.saldo,
                        fecha = _deuda.fecha,
                        estado = _deuda.estado
                    }).Single();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public double VerificarDeuda(int idSocio)
        {
            throw new NotImplementedException();
        }

        public Deuda ConsultarDeudaPorSocio(Socio socio)
        {
            string query = "SELECT * FROM cobrosdeudas CD " +
                            "LEFT JOIN cobros C ON CD.cobro_id = C.id " +
                            "LEFT JOIN cupones U ON C.cupon_id = U.id " +
                            "WHERE U.SOCIO_ID = @socio_id AND cd.estado = 'I'";
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Deuda>(query, new { socio_id = socio.id }).SingleOrDefault();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int ActualizarDeuda(Deuda _deuda)
        {
            string query = "UPDATE COBROSDEUDAS SET SALDO = @saldo, FECHA = @fecha WHERE ID = @deuda_id";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<int>(query, new
                    {
                        deuda_id = _deuda.id,
                        saldo = _deuda.saldo,
                        fecha = _deuda.fecha
                    }).Single();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public int ModificarDeuda(Deuda _deuda)
        {
            string query = "INSERT INTO COBROSDEUDAS (ID, COBRO_ID, SALDO, FECHA, ESTADO) VALUES (@deuda_id, @cobro_id, @saldo, @fecha, @estado) ON DUPLICATE KEY UPDATE ESTADO = @estado";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<int>(query, new
                    {
                        deuda_id = _deuda.id,
                        cobro_id = _deuda.cobro_id,
                        saldo = _deuda.saldo,
                        fecha = _deuda.fecha,
                        estado = _deuda.estado
                    }).Single();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public IList<Deuda> FindAll()
        {
            throw new NotImplementedException();
        }

        public IQueryable<Deuda> Find(System.Linq.Expressions.Expression<Func<Deuda, bool>> predicate)
        {
            throw new NotImplementedException();
        }

        public Deuda FindById(int id)
        {
            throw new NotImplementedException();
        }

        public int Add(Deuda newEntity)
        {
            throw new NotImplementedException();
        }

        public int Remove(int id)
        {
            throw new NotImplementedException();
        }

        public int Modify(Deuda entity)
        {
            throw new NotImplementedException();
        }
    }
}
